package edu.ccut.saturn.basecomponent.database.impl.sqlserver.builder.unused;

import java.util.ArrayList;
import java.util.List;

import edu.ccut.saturn.basecomponent.database.ISaturnSQL;
import edu.ccut.saturn.basecomponent.database.SaturnSQLBuilderFactory;
import edu.ccut.saturn.basecomponent.database.impl.AbstractSelectSQLBuilder;
import edu.ccut.saturn.basecomponent.database.impl.SaturnSQL;
import edu.ccut.saturn.basecomponent.database.util.SaturnDataUtil;
import edu.ccut.saturn.component.IDataInfo;
import edu.ccut.saturn.component.IDataItem;

public class DB2SelectSQLBuilder extends AbstractSelectSQLBuilder {
	
	private String SQL_SELECT_PAGE = "SELECT * FROM (SELECT ROWNUMBER() OVER() ROWNUM, A.* FROM(@resultSet@) A) B WHERE ROWNUM BETWEEN @startIndex@ AND @endIndex@"; 

	private static final String SQL_RESULTSET_PLACEHOLDER = "@resultSet@";

	private static final String SQL_STARTINDEX_PLACEHOLDER = "@startIndex@";

	private static final String SQL_ENDINDEX_PLACEHOLDER = "@endIndex@";
	
	private String tableWord;

	@Override
	protected String getDatabaseType() {
		return SaturnSQLBuilderFactory.DB2;
	}

	@Override
	protected String getOperationType() {
		return ISaturnSQL.SELECT_OPERATION;
	}

	@Override
	public ISaturnSQL build(String fullKey, String condition, int startIndex,
			int endIndex) throws Exception {
		
		String sql = getSql(fullKey, condition, startIndex, endIndex);

		SaturnSQL saturnsql = new SaturnSQL();
		
		saturnsql.setSql(sql);
		saturnsql.setDBtype(this.getDatabaseType());
		saturnsql.setSaturnData(null);
		saturnsql.setSaturnDataFullKey(fullKey);
		saturnsql.setOperationType(this.getOperationType());

		return saturnsql;
	}
	
	private String getSql(String fullKey, String condition, int startIndex,
			int endIndex) {

		IDataInfo dataInfo = SaturnDataUtil.getDataInfo(fullKey);
		
		String sqlResult = SQL_SELECT_PAGE;

		String sql = getSqlSelect(condition, dataInfo);
		
		sqlResult = sqlResult.replace(SQL_RESULTSET_PLACEHOLDER, sql);
		
		sqlResult = sqlResult.replace(SQL_STARTINDEX_PLACEHOLDER, String
				.valueOf(startIndex));
		
		sqlResult = sqlResult.replace(SQL_ENDINDEX_PLACEHOLDER, String
				.valueOf(endIndex));
		
		return sqlResult;
	}
	
	private String getSqlSelect(String condition, IDataInfo dataInfo) {
		StringBuffer sql = new StringBuffer();
		String tempSql = dataInfo.getSqlStr();

		if (tempSql != null && !"".equals(tempSql)) {
			sql.append(tempSql);

			if (condition != null) {
				sql.append(condition.replaceFirst("WHERE", " AND "));
			}

		} else {
			sql.append(" SELECT ");
			sql.append(getAfterSelectSql(dataInfo));

			if (condition != null) {
				sql.append(condition);
			}
		}

		return sql.toString();
	}

	private StringBuffer getAfterSelectSql(IDataInfo dataInfo) {

		StringBuffer selectColumnName = new StringBuffer();

		StringBuffer selectTableName = new StringBuffer();

		List<String> tableNamelist = new ArrayList<String>();

		for (String key : dataInfo.keySet()) {

			IDataItem iDataItem = dataInfo.getDataItem(key);

			String colum = iDataItem.getColumn();

			String columnAlias = iDataItem.getColumnAlias();

			String tableName = iDataItem.getTableName();

			if (tableName != null && !"".equals(tableName.trim())) {

				selectColumnName.append(tableName).append(".").append(colum);

				if (columnAlias != null && !columnAlias.equals("")) {

					selectColumnName.append(" AS ").append(columnAlias).append(
							" ,");

				} else {
					selectColumnName.append(" ,");
				}

				if (this.tableWord != null) {
					continue;
				}

				if (!tableNamelist.contains(tableName)) {

					tableNamelist.add(tableName);

					selectTableName.append(tableName).append(" ,");
				}
			}
		}

		deleteLastCommaWithStringBuffer(selectColumnName);

		selectColumnName.append(" FROM ");

		deleteLastCommaWithStringBuffer(selectTableName);

		if (this.tableWord != null) {

			selectColumnName.append(this.tableWord).append(" ");

		} else {

			selectColumnName.append(selectTableName);

		}

		return selectColumnName;
	}

	private void deleteLastCommaWithStringBuffer(StringBuffer stringBuffer) {
		if (stringBuffer.length() != 0)
			stringBuffer.deleteCharAt(stringBuffer.length() - 1);
	}

	private void setTableWord(String tableWord) {
		this.tableWord = tableWord;
	}

	@Override
	public ISaturnSQL build(String fullKey, String condition,
			String partitionWord, int startIndex, int endIndex)
			throws Exception {
		
		if (tableWord != null) {
			setTableWord(tableWord);
		}
		return this.build(fullKey, condition, startIndex, endIndex);
	}
}
